package logic;
/**
 * This class holds the methods that bridge the server and the database
 * Methods are called by the server instance, are executed in this class
 * returned and passed on to the client.
 */
import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.sql.rowset.serial.SerialBlob;

import common.CommonMethods;

import Entities.AssessmentEntity;
import Entities.EmailEntity;
import Entities.ExecutionEntity;
import Entities.ExtendEntity;
import Entities.InspectionEntity;
import Entities.InspectionEntity;
import Entities.RequestEntity;
import Entities.ReviewAndDecisionEntity;
import Entities.UserEntity;

public class MySQLconnection {

private static final String String = null;
private Connection conn;
	
public MySQLconnection (String sqlServer, String sqlUsername, String sqlPassword)
{
	 try 
	 	{
	         Class.forName("com.mysql.jdbc.Driver").newInstance();
	    } catch (Exception ex) {/* handle the error*/}
	 
	try {
	

		//this.conn=DriverManager.getConnection("jdbc:mysql://localhost/test","root","1234");
		//this.conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","1234");
		this.conn=DriverManager.getConnection("jdbc:" + sqlServer,sqlUsername,sqlPassword);





		
	//	System.out.println("SQL connection succeed");
	} catch (SQLException ex) {
		// TODO Auto-generated catch block
		//System.out.println("SQLException: " + ex.getMessage());
      //  System.out.println("SQLState: " + ex.getSQLState());
      //  System.out.println("VendorError: " + ex.getErrorCode());
	}
}

/**
 * 
 * This method get a user enitity with the username and password
 * and if a match is found, allows access and fetches the user's access level
 * @param log
 * @return
 */
public ArrayList<Object> login(UserEntity log)
	{	
		int[] previlage= new int[6];
		String uid=null;
		ArrayList<Object> answer=new ArrayList<Object>();
		 try {
			Statement stmt= conn.createStatement();
			Statement stmt1=conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM users;");
			
			while(rs.next())
			{
			
				
				if(((rs.getString(2))).equals(log.getUserName()))
				{
					uid = rs.getString(1);
					
					if(rs.getString(10).equals("yes"))
					{
						answer.add("User is alerady Logged in !");
						return answer;
					}
					if((rs.getString(3)).equals(log.getPassword()))
					{
					
						answer.add("Allowed");
						for(int i=4;i<=9;i++)
						{
							previlage[i-4]=	rs.getInt(i);
						
						}
						answer.add(previlage);
						answer.add(rs.getString(1));
						
						
					
					}
			   }
		   }
			if(!uid.equals(null))
			{
				stmt1.executeUpdate("UPDATE test.users SET isLoggedIn = 'yes' WHERE Uid ='"+uid+"';");
			}
			
			stmt1.close();
			stmt.close();
			 rs.close();
			 return answer;
			 
		} 
			catch (SQLException e) {
			// TODO Auto-generated catch block
		//	e.printStackTrace();
		}
		
		 answer.add("Not Allowed");
		 answer.add(0);
		return answer;
		
	}

/**
 * 
 * 
 * @param String userID
 * This method fetches the user information from the outer table in the DB
 * and returns it
 * @return an ArrayList with the user information
 */

public ArrayList<Object> getInfo(String userID)
{
	
	ArrayList<Object> userInfo = new ArrayList<Object>();
	
	userInfo.add("userInfo");
	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.outer WHERE Oid =" + "'" + userID + "'" + ";");

		
	
		if(rs.next())
		{
		userInfo.add(rs.getString(2)); //Get First Name
		userInfo.add(rs.getString(3)); //Get Last Name
		userInfo.add(rs.getString(4)); //Get E-Mail
		userInfo.add(rs.getString(6)); //Get Title
		userInfo.add(rs.getString(7)); //Get Department
		userInfo.add(rs.getString(5)); //Get Phone
		}
		else
		{
			userInfo.add("No User Error.");
		}
		stmt.close();
		rs.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		userInfo.add("No User Error.");
	//	e.printStackTrace();
	}
	
	return userInfo;
}

/**
 * 
 * @param request is a request entity holding the information sent from the client
 * 	The method creates a new request in the Systems
 * @return returns Valid if all fields are acceptable, Invalid otherwise
 */
public ArrayList<Object> request (RequestEntity request)
{
	
	String sysid;
	String aid;
	
	ArrayList<Object> answer=new ArrayList<Object>();
	
	answer.add("returnedRequest");

	try{
		Statement stmt2= conn.createStatement();
		ResultSet rs = stmt2.executeQuery("SELECT Sysid FROM systeminfo WHERE Description = " + "'" + request.getSystem() + "';"); // get the Sysid for the selected system
		rs.next();
		sysid = rs.getString(1);
		Statement stmt3= conn.createStatement();
		ResultSet rs2 = stmt3.executeQuery("SELECT assessorid FROM systemAssessors WHERE systemid = " + "'"+ sysid +"';"); // get assessor suitable for the system
		rs2.next();
		aid = rs2.getString(1);
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("INSERT INTO request(Sysid,Aid,CurrentDescription,ChangeDescription,Explanation,Date,AdittionalData,Uid1,Status,Deadline) VALUES('" + sysid + "','" + aid +"','" + request.getCSD() + "','" + request.getOCD() +"','" + request.getEXP() +"','"+ request.getD() + "','" + request.getFIN() +"','" + request.getUid() + "','1','Not yet set');");

		if ( !request.getRE().isEmpty() )
		{
			Statement stmt4 = conn.createStatement();
			stmt4.executeUpdate("UPDATE request SET Remarks = '" + request.getRE() + "' WHERE Rid = LAST_INSERT_ID();");
			stmt4.close();
		}
	    stmt.close();
	    stmt2.close();
	    stmt3.close();
	    rs.close();
	    rs2.close();
	    answer.add("Valid");
	    
	}
	catch (SQLException e)
	{
	// TODO Auto-generated catch block
	//e.printStackTrace();
	answer.add("Invalid");
	}
	return answer;
	
	// TODO Auto-generated method stub
	
}
/**
 * 
 * This method takes sets the duration of a request in the Execution phase
 * @param log
 * @return ArrayList<Object> 
 */
public ArrayList<Object> setExecutionDuration(int log){
	
	ArrayList<Object> answer=new ArrayList<Object>();
	 try {
			Statement stmt= conn.createStatement();
			
			Statement stmt2= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM request where Stid = '20';");	
			answer.add("TotalAnswer");
			rs.last();
			answer.add(rs.getRow());
			rs.first();
			do
			{
				//if((int)answer.get(1)== 0) break;
				if(Integer.parseInt((answer.get(1)).toString())== 0) break;
					int uidRes ;
					uidRes = rs.getInt(14);
					PreparedStatement ps1= conn.prepareStatement("SELECT * FROM users where Uid = ? ;");
				
					ps1.setInt(1, uidRes);
					ps1.executeQuery();
					
					
		   }while(rs.next());
			stmt.close();
			stmt2.close();
			 rs.close();
	 	}
	 catch ( Exception e) {
		 
		
	 }
	// if ((int)answer.get(1)!= 0){
	 if (Integer.parseInt(answer.get(1).toString())!= 0){
		 return answer;
	 }
	 else {
		 answer.add(0,"Empty");
		 return answer;
	 }
}


/**
 * 
 * This method builds a list of past requests that were made by the given user
 * @param userID - the user ID
 * @return a list of past requests
 * 
 */
public ArrayList<Object> getRequestHistory(String userID)
{
ArrayList<Object> listOfRequests = new ArrayList<Object>();
	
listOfRequests.add("listOfRequestHistory");
	try {
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.request WHERE Uid1 = '" + userID +"';");
		ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test.request WHERE Uid1 = '" + userID +"';");

		rs.next();
		listOfRequests.add(rs.getString(1)); // number of rows in the table
			
		while(rs2.next())
		{
			listOfRequests.add(rs2.getString(1)); // add request id to the list
		}
		
		stmt.close();
		stmt2.close();
		rs.close();
		rs2.close();
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		listOfRequests.add("0");
		listOfRequests.add("EmptyList");
			//e.printStackTrace();
	}
	
	return listOfRequests;
}
/**
 * 
 * This method build a request entity for transfering between the client and the databse
 * @param requestID- the request ID
 * @return returns an ArrayList holding the entity
 */
public ArrayList<Object> getRequest(String requestID)
{

	ArrayList<Object> requestData = new ArrayList<Object>();
	
	requestData.add("requestData");
	

	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.request WHERE Rid =" + "'" + requestID + "'" + ";");
		
		rs.next();
		String sysid = new String (rs.getString(2));
		String aid = new String (rs.getString(3));
		String eid = rs.getString(4);
		String currentDescription = rs.getString(5);
		String changeDescription = rs.getString(6);
		String explanation = rs.getString(7);
		String duration = rs.getString(8);
		String date = rs.getString(9);
		String deadline = rs.getString(10);
		String remarks = rs.getString(11);
		String fileName =rs.getString(12);
		String uid = rs.getString(13);
		String statusid = rs.getString(14);
		  
		Statement stmt2= conn.createStatement();
		ResultSet rs2 = stmt.executeQuery("SELECT Description FROM test.systeminfo WHERE Sysid = '" + sysid + "';"); // get System description
		
		rs2.next();
		String system =rs2.getString(1);
		
		Statement stmt3= conn.createStatement();
		ResultSet rs3 = stmt.executeQuery("SELECT Description FROM test.status WHERE Stid = '" + statusid + "';"); // get Status description
		
		rs3.next();
		String status = rs3.getString(1);
		
	
		RequestEntity request = new RequestEntity ( currentDescription, changeDescription, explanation, remarks, null,null, date, null, null, fileName, uid,  system);
		Integer rid=Integer.parseInt(requestID);	
		request.setrid(rid);
		
		request.setStatusid(statusid);
		request.setStatus(status);
		if (deadline!=null)
			request.setDeadline(deadline);
		else
			request.setDeadline("Not yet set");
		if (remarks.isEmpty())
			request.setRemarks("empty");
		request.setStatusid(statusid);
		requestData.add(request);
		stmt.close();
		stmt2.close();
		stmt3.close();
		
		rs.close();
		rs2.close();
		rs3.close();
		requestData.add("success");
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		requestData.add("No request Error.");
	//	e.printStackTrace();
	}
	

	
	return requestData;
}

/**
 * 
 * This general method finds all requests in the giving status that match the giving userID
 * @param userID- the user logged in id
 * @param currentStatus- the requests status
 * @return an ArrayList with the number of rows followed by a list of values
 */
public ArrayList<Object> getlistOfRequests(String userID, String currentStatus)
{
ArrayList<Object> listOfRequests = new ArrayList<Object>();

listOfRequests.add("listOfRequests");
try {
Statement stmt= conn.createStatement();
Statement stmt2= conn.createStatement();



ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.request WHERE Status= '"+currentStatus+"' && Aid=" + "'" + userID + "'" + ";");
ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test.request WHERE Status='"+currentStatus+"' && Aid=" + "'" + userID + "'" + ";");

rs.next();
listOfRequests.add(rs.getString(1)); // number of rows in the table

while(rs2.next())
{
listOfRequests.add(rs2.getString(1)); 
}

stmt.close();
stmt2.close();
rs.close();
rs2.close();


} catch (SQLException e) {
// TODO Auto-generated catch block
listOfRequests.add("0");
listOfRequests.add("EmptyList");
//e.printStackTrace();
}

return listOfRequests;
}
///////////////////////////// case 101
/**
 * 
 * This method sets the request duration for the existing phase
 * @param request- request number
 * @param days- number of days to set
 * @param newStatus- status to set after complete
 * @return Valid\notValid
 */
public ArrayList<Object> setTaskDuration(String request, String days, String newStatus)
{
	ArrayList<Object> TaskDuration = new ArrayList<Object>();
	TaskDuration.add("TaskDuration");

	try
	{
		Statement stmt = conn.createStatement();
		Statement stmt1 = conn.createStatement();
		int result = stmt.executeUpdate("UPDATE test.request SET Duration = '" + days + "' WHERE Rid =" +request);
		stmt1.executeUpdate("UPDATE test.request SET Status = '" + newStatus + "' WHERE Rid =" +request);
		stmt.close();
		stmt1.close();
		if (result ==1)
			TaskDuration.add("TaskDurationSet");
		else
			TaskDuration.add("TaskDurationNotSet");
	}
	catch (SQLException e)
	{
		// TODO Auto-generated catch block
		TaskDuration.add("TaskDurationNotSet");
		//e.printStackTrace();
	}
return TaskDuration;
}

/**
 * 
 * @param status is a parameter indicating a certain wanted request status
 * 	The method searches the DB for lines with the compatible status 
 * @return returns all requests ID that have the status or emptylist1 if non are found
 */
public ArrayList<Object> getReq(String status)
{
	ArrayList<Object> reqid = new ArrayList<Object>();
	reqid.add("ridlist");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.request WHERE Status= "+status);
	ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test.request WHERE Status="+status);

	rs.next();
	reqid.add(rs.getString(1)); // number of rows in the table
	while(rs2.next())
	{	
		reqid.add(rs2.getString(1)); // add requests to the list
	}
	stmt.close();
	stmt2.close();
	rs.close();
	rs2.close();
	if(reqid.get(1).equals("0"))
	{
		reqid.add("0");
		reqid.add("EmptyList1");
	}

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		reqid.add("0");
		reqid.add("EmptyList1");
//	e.printStackTrace();
	}

	return reqid;
	
}



/**
 * 
 * This method asks for all the requests in the giving Status.
 * @param userID
 * @param currentStatus
 * @param column
 * @param table
 * @return ArrayList<Object> with the number of Entities and the entities it self.
 */
public ArrayList<Object> getlistOfExRequests(String userID,String currentStatus,String column,String table)
{
ArrayList<Object> listOfExRequests = new ArrayList<Object>();

listOfExRequests.add("listOfExRequests");
try {
Statement stmt= conn.createStatement();
Statement stmt2= conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test." +table+ " WHERE Status= '"+currentStatus+"' && " +column+ "=" + "'" + userID + "'" + ";");
ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test." +table+ " WHERE Status='"+currentStatus+"' && " +column+ "=" + "'" + userID + "'" + ";");

rs.next();
listOfExRequests.add(rs.getString(1)); // number of rows in the table
while(rs2.next())
{
	listOfExRequests.add(rs2.getString(1)); // add systems to the list
}
stmt.close();
stmt2.close();
rs.close();
rs2.close();

} catch (SQLException e) {
//TODO Auto-generated catch block
listOfExRequests.add("0");
listOfExRequests.add("EmptyList");
//e.printStackTrace();
}
return listOfExRequests;
}

/**
 * 
 * This general method counts the number of rows in a table and then builds a list of all values in a given column
 * @return an ArrayList with the number of rows followed by a list of values
 */

public ArrayList<Object> getListOfColumn(String table, String columnName, String expectedCode)
	{
		ArrayList<Object> listOfColumn = new ArrayList<Object>();
		
		listOfColumn.add(expectedCode);
		try {
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test." + table + ";");
			ResultSet rs2 = stmt2.executeQuery("SELECT " + columnName + " FROM test." + table + ";");

			rs.next();
			listOfColumn.add(rs.getString(1)); // number of rows in the table
				
			while(rs2.next())
			{
				listOfColumn.add(rs2.getString(1)); // add listOfColumn rows to the list
			}
			
			stmt.close();
			stmt2.close();
			rs.close();
			rs2.close();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			listOfColumn.add("0");
			listOfColumn.add("EmptyList");
			//	e.printStackTrace();
		}
		
		return listOfColumn;
}
/**
 * 
 * this method creates a Review and Decision entity using a given assessment id
 * and returns it
 *
 * @param assID assessment ID

 * @return review and decision entity
 */
public ArrayList<Object> getReviewAndDecision(String rid)
{
	ArrayList<Object> answer = new ArrayList<Object>();
		
	answer.add("returnedReviewAndDecision");

	
	try {
			
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		Statement stmt3= conn.createStatement();
		Statement stmt4= conn.createStatement();
		Statement stmt5= conn.createStatement();
		
		
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.reviewanddecision WHERE reqID = '" + rid +"';");
		ResultSet rs2 = stmt2.executeQuery("SELECT Deadline FROM test.request WHERE Rid = '" + rid +"';");
		
		
		ReviewAndDecisionEntity rnd = new ReviewAndDecisionEntity();
		
		rs2.next();
		rnd.setDeadline(rs2.getString(1));
		
		answer.add("Valid");

		if (!rs.next()) // returns false if empty
		{
			//answer.add("No Review");
			stmt3.executeUpdate("UPDATE test.request SET Status = '6' WHERE Rid = '" + rid + "';"); 
			stmt4.executeUpdate("INSERT INTO test.reviewanddecision SET rndID = NULL, reqID = '" + rid + "', reviewAndDecision = '', wasExtended = 'no';");
			rnd.setreqID(rid);
			rnd.setwasExtended("no");
			// now add 7 days to the deadline
		//	String newdeadline = CommonMethods.addDurationToDate(rnd.getdeadline(), 7);
			//rnd.setDeadline(newdeadline);
		//	stmt5.executeUpdate("UPDATE test.request SET deadline = '" + newdeadline + "' WHERE Rid = '" + rid + "';");
		}
		else
		{	
			
			//answer.add("Has review");
			rnd.setrndID(rs.getString(1));
			rnd.setreqID(rs.getString(2));
			rnd.setrndLog(rs.getString(3));
			rnd.setwasExtended(rs.getString(4));
		
			
		}
		
		
		answer.add(rnd);	
		
		stmt.close();
		stmt2.close();
		rs.close();
		rs2.close();
		
					
	} 
	catch (SQLException e)
	{
		answer.add("Error: No Review and Decision found");
	//	e.printStackTrace();
	}	

return answer;
}

/**
 * 
 * @param assID Assessment ID
 *  This method convert an Assessment ID to a Request ID
 * @return request ID
 */
public String assIDtoRid (String assID)
{
	String rid = "No Rid found";
		try {
			Statement stmt= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT Rid FROM test.assessment WHERE Assid = '" + assID +"';");
			
			rs.next();
			rid = new String (rs.getString(1));
			
			stmt.close();
			rs.close();
						
		} catch (SQLException e)
		{
			//e.printStackTrace();
		}
		
		
	
	
	return rid;
}

/**
 * 
 * @param rid - Request ID
 *  This method convert a Request ID to an Assessment ID
 * @return Assessment ID
 */
public String ridToAssid (String rid)
{
	String assID = "No assID found";
		try {
			Statement stmt= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT Assid FROM test.assessment WHERE Rid = '" + rid +"';");
			
			rs.next();
			rid = new String (rs.getString(1));
			
			stmt.close();
			rs.close();
						
		} catch (SQLException e)
		{
		//	e.printStackTrace();
		}
		
		
	
	
	return rid;
}

/**
 * 
 * This method updates a value in a column at a table, as sent by the caller
 * and returns an array list made of the expected string chosen by the caller and the value Valid or Invalid
 * @param table - Table name
 * @param column - Column name
 * @param RidColumnName - Name of the Rid column in the table
 * @param requestID - value of request ID
 * @param value - Value to set
 * @param expectedCode - Returned code expected by the caller
 * @return
 */
public ArrayList<Object> setValueByRequestID (String table, String column, String ridColumnName, String requestID, String value, String expectedCode )
{	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(expectedCode);
	try
	{
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("UPDATE test."+ table + " SET " + column + " = '" + value + "' WHERE " + ridColumnName + " = '" + requestID + "';");
		answer.add("Valid");		
		stmt.close();
	}
	catch (SQLException e)
	{
		answer.add("Invalid");
	//	e.printStackTrace();
	}
return answer;
}


/**
 * 
 * This method saves the review and decision logs to the database.
 * The method uses the cleanStringForQuery method in the CommonMethods class
 * @param requestID the request ID
 * @param reviewAndDecision the discussion log
 * @return validation of the operation
 */
public ArrayList<Object> insertValuesToReviewAndDecision (String requestID, String reviewAndDecision )
{	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("discussionLogAnswer");
	
	requestID = requestID;
	reviewAndDecision = CommonMethods.cleanStringForQuery(reviewAndDecision);
	//System.out.println(reviewAndDecision);
	try
	{
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("INSERT INTO test.reviewanddecision SET rndID =NULL, reqID = '" + requestID + "', reviewAndDecision = '" + reviewAndDecision + "', wasExtended = 'no';");
		
		answer.add("Valid");		
		stmt.close();
	}
	catch (SQLException e)
	{
		answer.add("Invalid");
		//e.printStackTrace();
	}
return answer;
}



/**
 * 
 * @param assessmentID - Assessment ID
 * @return This method returns an assessment entity
 */
public ArrayList<Object> getAssessment (String assessmentID)
{

	ArrayList<Object> assessmentData = new ArrayList<Object>();
	
	assessmentData.add("assessmentData");
	

	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.assessment WHERE Assid =" + "'" + assessmentID + "'" + ";");
		
		rs.next();
		String rid = new String (rs.getString(2));
		String location = new String (rs.getString(3));
		String durationAssessment = rs.getString(4);
		String changeDescription = rs.getString(5);
		String benefit = rs.getString(6);
		String constrictions = rs.getString(7);
		
		
		Statement stmt2= conn.createStatement();
		ResultSet rs2 = stmt.executeQuery("SELECT Aid FROM test.request WHERE Rid = '" + rid + "';"); // get Assessor ID
		
		rs2.next();
		String assessorID = rs2.getString(1);
		
		
		AssessmentEntity assessment = new AssessmentEntity ();
		assessment.setAssessmentID(assessmentID);
		assessment.setAssessorID(assessorID);
		assessment.setBenefit(benefit);
		assessment.setChangeDescription(changeDescription);
		assessment.setConstrictions(constrictions);
		assessment.setDurationAssessment(durationAssessment);
		assessment.setLocation(location);
		assessment.setRid(rid);
		
		assessmentData.add("Valid");
		assessmentData.add(assessment);
		
		stmt.close();
		stmt2.close();
		
		rs.close();
		rs2.close();
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		assessmentData.add("No assessment Error.");
	//	e.printStackTrace();
	}
	
	return assessmentData;
}

/**
 * 
 * This method returns a list of rows for two conditions.
 * @param table - table name
 * @param columnToTest - column to compare with the values (columns to use with the WHERE condition)
 * @param columnToReturn - values of this column are returned (column to use with SELECT)
 * @param value1 - first value to compare
 * @param value2 - second value to compare
 * @param expectedCode - code to be expected by the receiver
 * @return this method returns an ArrayList holding the expected code,
 * followed by the number of values ahead of it and from there on, values that answer either conditions (value 1 OR value 2)
 */
public ArrayList<Object> getRequestsWithTwoValues(String table, String columnToTest, String columnToReturn, String value1, String value2, String expectedCode)
{
	ArrayList<Object> listOfRequests = new ArrayList<Object>();
	
	listOfRequests.add(expectedCode); // Add the expected code first
	try {
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		Statement stmt3= conn.createStatement();
		Statement stmt4= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test." + table + " WHERE " + columnToTest + " = '" + value1 + "';"); // count rows fitting value 1
		ResultSet rs2 = stmt2.executeQuery("SELECT " + columnToReturn + " FROM test." + table + " WHERE " + columnToTest + " = '" + value1 + "';"); // get list of rows 
		ResultSet rs3 = stmt3.executeQuery("SELECT COUNT(*) FROM test." + table + " WHERE " + columnToTest + " = '" + value2 + "';"); // count rows fitting value 2
		ResultSet rs4 = stmt4.executeQuery("SELECT " + columnToReturn + " FROM test." + table + " WHERE " + columnToTest + " = '" + value2 + "';"); // get list of rows

		rs.next();
		rs3.next();
		
		int count = Integer.parseInt(rs.getString(1)) + Integer.parseInt(rs3.getString(1)); // Sum the result of both count queries
		
		listOfRequests.add(Integer.toString(count)); // number of rows in the table fitting either value
			
		while(rs2.next())
		{
			listOfRequests.add(rs2.getString(1)); // add the list rows to the list for value1
		}
		
		while(rs4.next())
		{
			listOfRequests.add(rs4.getString(1)); // add the list rows to the list for value2
		}
		
		stmt.close();
		stmt2.close();
		stmt3.close();
		stmt4.close();
		rs.close();
		rs2.close();
		rs3.close();
		rs4.close();
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		listOfRequests.add("0");
		listOfRequests.add("EmptyList");
		//	e.printStackTrace();
	}
	
	return listOfRequests;

}

/**
 * 
 * This method simply converts a request ID to an Assessment ID
 * by accessing the request table
 * @param requestID
 * @return
 */
public ArrayList<Object> convertRequestIDtoAssessmentID(String requestID)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	String assessmentID = ridToAssid(requestID);
	answer.add("ridConveretedToAssid");
	answer.add(assessmentID);
	
	return answer;
}
/**
 * 
 * @param 
 * 	The method searches the DB for users that are assessors
 * @return returns all users Id that are assessors
 */
public ArrayList<Object> getAss() {
	// TODO Auto-generated method stub
	ArrayList<Object> assessor = new ArrayList<Object>();
	assessor.add("assessor");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.users WHERE Assessor= '1';");
	ResultSet rs2 = stmt2.executeQuery("SELECT Uid FROM test.users WHERE Assessor='1';");

	rs.next();
	assessor.add(rs.getString(1)); // number of rows in the table
	while(rs2.next())
	{	
		assessor.add(rs2.getString(1)); // add requests to the list
	}

	stmt.close();
	stmt2.close();
	rs.close();
	rs2.close();
	if(assessor.get(1).equals("0"))
	{
		assessor.add("0");
		assessor.add("EmptyList1");
	}

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		assessor.add("0");
		assessor.add("EmptyList1");
	//e.printStackTrace();
	}

	return assessor;
}
/**
 * 
 * 
 * This method inserts a new row to the executed table in order to document for further use.  
 * @param code
 * @param rid1
 * @param det
 * @param bool
 * @return Valid\inValid
 */
public ArrayList<Object> enterNewExecutionRecord (String code,String rid1, String det, String bool)
{	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(code);
	int rid = Integer.parseInt(rid1);
	try
	{
		Statement stmt= conn.createStatement();
		//stmt.executeUpdate("UPDATE test."+ table + " SET " + column + " = '" + value + "' WHERE " + ridColumnName + " = '" + requestID + "';");
		stmt.executeUpdate("INSERT INTO test.executed (rid1,executionDetails,wasExtended) VALUES ('"+rid+"','"+det+"','"+bool+"');");
		answer.add("Valid");		
		stmt.close();
	}
	catch (SQLException e)
	{
		answer.add("Invalid");
		//e.printStackTrace();
	}
return answer;
}
/**
 * 
 * This method changes the Status number in the main request table by request id .
 * @param request
 * @param newStatus
 * @return Valid\notValid
 */
public ArrayList<Object> changeStatus(String request,String newStatus)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("changeStatus");
	try {
		Statement stmt = conn.createStatement();
		int res=stmt.executeUpdate("UPDATE test.request SET Status = '" + newStatus + "' WHERE Rid =" +request);
		stmt.close();
		if (res==1)
			answer.add("Valid");
		else
			answer.add("statusNotSet");
	}catch (SQLException e){
		// TODO Auto-generated catch block
		answer.add("statusNotSet");
		//e.printStackTrace();
	}
return answer;
}
/**
 * 
 * This method creates a request for a new deadline.
 * It is generalized and will handle requests from every status
 * @param deadlineRequest - an ExtendEntity holding the parameters for the query
 */
public ArrayList<Object> extendDeadlineRequest(ExtendEntity deadlineRequest) 
{
	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("extentionRequestWasSent");
	
	try
	{
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		stmt.executeUpdate("INSERT INTO test.extendDeadlineRequests SET ExtID =NULL, requestID = '" + deadlineRequest.getRequestID() + "', Reason = '" + deadlineRequest.getReason() + "', oldStatus = '" + deadlineRequest.getOldStatus() + "', requestedDeadline = '" +deadlineRequest.getNewDeadline() + "';");
		setValueByRequestID(deadlineRequest.getCurrentTable(), deadlineRequest.getWasExtendedColumnName(), deadlineRequest.getRequestIDColumnName(), deadlineRequest.getRequestID(), "yes", "dummyCode");
		
		stmt.close();
		stmt2.close();
		answer.add("Valid");
	}
	catch (SQLException e)
	{
		
		//e.printStackTrace();
		answer.add("Invalid");
		
	}
	
//	System.out.println("All done in the query");
	return answer;


}

/**
 * 
 * This method returns assessments back to their preassessment state for reassessment;
 * @param requestID Request ID number
 * @param additionalInformation Information required by the committee
 * @return signals the client to reload the list of requests
 */
public ArrayList<Object> requireAdditionalInformation(String requestID, String additionalInformation)
{
	changeStatus(requestID, "4"); // reset to preAssessment status
	setValueByRequestID ("assessment","reviewed", "Rid", requestID, CommonMethods.cleanStringForQuery(additionalInformation),  "dummy" ); // update reviewed column
	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("changeStatus");
	
	return answer;

}
/**
 * 
 * @param userID is the Uid of the wanted user
 * 	The method searches the DB for the line containing the users info 
 * @return array list containing the desired info, or error
 */
public ArrayList<Object> getUser(String userID) {
	// TODO Auto-generated method stub
ArrayList<Object> userData = new ArrayList<Object>();
	
userData.add("requestUser");
	

	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.users WHERE Uid =" + "'" + userID + "'" + ";");
		
		rs.next();
		userData.add(rs.getString(2));
		stmt.close();
		
		rs.close();
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		userData.add("No request Error.");
		//e.printStackTrace();
	}
	return userData;


}
/**
 * 
 * @param reqID is a certain request id
 * @param ID is a certain assessors ID 
 * @param identity is the filed we want to update 
 * @param status is the status code in the request line to be updated
 * 	The method searches the DB for a field to update its ID and status
 * @return AbleToPair if succeeded or NotAbleToPair otherwise 
 */
public ArrayList<Object> Pair(String reqID, String ID ,String identity,String status) {
	// TODO Auto-generated method stub
	ArrayList<Object> pairStatus = new ArrayList<Object>();
	try {
		Statement stmt= conn.createStatement();
		int result = stmt.executeUpdate("UPDATE test.request SET "+identity+" = " + ID + " WHERE Rid =" +reqID);
		stmt.executeUpdate("UPDATE test.request SET Status = '" + status + "' WHERE Rid =" +reqID);
		if (result == 1)
			pairStatus.add("AbleToPair");
		else
			pairStatus.add("NotAbleToPair");
		stmt.close();
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		pairStatus.add("NotAbleToPair");
		//e.printStackTrace();
	}
	
	return pairStatus;

}
/**
 * 
 * @param 
 * 	The method searches the DB for users who are Execution Leaders
 * @return array list containing the ID of the person
 */
public ArrayList<Object> getExe() {
	// TODO Auto-generated method stub
	ArrayList<Object> exeleader = new ArrayList<Object>();
	exeleader.add("executionLeader");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.users WHERE ExecutionLeader= '1';");
	ResultSet rs2 = stmt2.executeQuery("SELECT Uid FROM test.users WHERE ExecutionLeader='1';");
	rs.next();
	exeleader.add(rs.getString(1)); 
	while(rs2.next())
	{	
		exeleader.add(rs2.getString(1)); 
	}
	
	stmt.close();
	stmt2.close();
	rs.close();
	rs2.close();
	if(exeleader.get(1).equals("0"))
	{
		exeleader.add("0");
		exeleader.add("EmptyList1");
	}
	} catch (SQLException e) {
	// TODO Auto-generated catch block
		exeleader.add("0");
		exeleader.add("EmptyList");
	//e.printStackTrace();
	}

	return exeleader;
	
}
/**
 * 
 * This method updates the database with the assessment details 
 * @param assessmentEntity
 * @param newStatus
 * @return an ArrayList with 'AssessmentSet' if succeeded 
 */



public ArrayList<Object> setAssessment(AssessmentEntity assessmentEntity, String newStatus)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("setAssessment");
	try {	
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			stmt.executeUpdate("UPDATE test.assessment SET Place= '" + assessmentEntity.getLocation() + "' , DurationAss='" +assessmentEntity.getDurationAssessment()+"' , ChangeDescription='" +assessmentEntity.getChangeDescription()+ "' , Benefit='" +assessmentEntity.getBenefit()+ "' , Constrictions='" +assessmentEntity.getConstrictions()+"' WHERE Rid =" + assessmentEntity.getRid());
			stmt2.executeUpdate("UPDATE test.request SET Status = '" + newStatus + "' WHERE Rid =" +assessmentEntity.getRid());
			stmt.close();
			stmt2.close();
			answer.add("AssessmentSet");
		}catch (SQLException e){
		// TODO Auto-generated catch block
			answer.add("AssessmentNotSet");
		//	e.printStackTrace();
		}	
	return answer;
}
/**
 * 
 * This method is used in order to insert a new record into the Exceeded table.
 * @param code
 * @param rid
 * @param uid
 * @param number
 * @param status
 * @return Valid\notValid
 */
public ArrayList<Object> fillInExceeded (String code,int rid , String uid , int number , String status)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(code);
	int uid1 =Integer.parseInt(uid);
	int status1 = Integer.parseInt(status);
	try {
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("INSERT INTO test.exceeded (rid,uid,exceedTime,status) VALUES ('"+rid+"','"+uid1+"','"+number+"','"+status1+"');");
		
		
		stmt.close();
		answer.add("valid");
		
	}
	catch(Exception e){
		answer.add("notValid");
	}
	return answer;
}
/**
 * 
 * This method searches for a specific requests by reqID witch in the Execution phase ,and returns it.  
 * @param code
 * @param reqID
 * @return array with all the requested request.
 */
public ArrayList<Object> getExeccutionEntity (String code,int reqID){
	ArrayList<Object> answer = new ArrayList<Object>();
	ExecutionEntity en = new ExecutionEntity();
	answer.add(code);
	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.executed WHERE rid1 =" + "'" + reqID + "'" + ";");
		rs.next();
		en.setRid(rs.getInt(2));
		en.setExecutionDetails(rs.getString(3).toString());
		en.setWasExtended(rs.getString(5));
		stmt.close();
		answer.add("Valid");
		answer.add(en);		
		
	}
	catch(Exception e)
	{
		answer.add("notValid");
	}
	return answer;
}
/**
 * 
 * @param reqID is the wanted request ID
 * @param ststus is the new status implicated after performed task
 * @param date is the start date of the request
 * @param duration is the assigned task duration for the current stage (in days)
 * @param newDeadline is the new end date for the request- date+duration
 * The method searches the DB for the line containing desired rid
 * @return array list GotIT if succeeded Fail otherwise
 */
public ArrayList<Object> approveEXDu(String reqID, String status) {
	// TODO Auto-generated method stub
	ArrayList<Object> approveEXDu = new ArrayList<Object>();
	String date;
	int duration;
	String newDeadLine;
	


	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();
	Statement stmt3= conn.createStatement();
	Statement stmt4= conn.createStatement();
	Statement stmt5= conn.createStatement();
	
	
	
	ResultSet rs = stmt.executeQuery("SELECT Duration FROM test.request WHERE Rid="+reqID);
	ResultSet rs2 = stmt2.executeQuery("SELECT Date FROM test.request WHERE Rid="+reqID);

	try
	{
		rs.next();
		duration= rs.getInt(1);
			
		rs2.next();
		date= rs2.getString(1);

	
	newDeadLine=CommonMethods.addDurationToDate(date,duration);
	
	
	stmt3.executeUpdate("UPDATE test.request SET Deadline = '" + newDeadLine + "' WHERE Rid =" +reqID);
	stmt4.executeUpdate("UPDATE test.request SET Duration = '0' WHERE Rid =" +reqID);
	
	}
	
	catch (SQLException e) {
		// TODO Auto-generated catch block
			approveEXDu.add("Fail");
	//	e.printStackTrace();
		}

	changeStatus(reqID,status);
	
	stmt.close();
	stmt2.close();
	stmt3.close();
	stmt4.close();
	stmt5.close();
	
	rs.close();
	rs2.close();
	approveEXDu.add("GotIT");
	} catch (SQLException e) {
	// TODO Auto-generated catch block
		approveEXDu.add("Fail");
	//e.printStackTrace();
	}
	return approveEXDu;
}

/**
 * 
 * This method builds a list of deadlines for a given request stauts in the system
 * @param status representing the number of the status
 * @return a list of deadlines of that status
 */
public ArrayList<Object> getlistOfDeadlines (String status)
{
	
		ArrayList<Object> listOfdeadlines = new ArrayList<Object>();
		listOfdeadlines.add("deadlineList");
		try {
	
		Statement stmt2= conn.createStatement();

	
		ResultSet rs2 = stmt2.executeQuery("SELECT Deadline FROM test.request WHERE Status ='"+status+"';");

	

		while(rs2.next())
		{
			listOfdeadlines.add(rs2.getString(1)); // add deadlines to the list
		
		}

	
		stmt2.close();
	
		rs2.close();

		} 
		catch (SQLException e)
		{
		// TODO Auto-generated catch block
			listOfdeadlines.add("0");
			listOfdeadlines.add("EmptyList");
		//e.printStackTrace();
		}
		
	return listOfdeadlines;
	
	
}


/**
 * 
 * This method builds a list of requests in a given status and their deadlines
 * used for building a list of those requests
 * @param status
 * @return a list of requests and deadlines
 */
public ArrayList<Object> getListsForAssignment(String status) 
{
	ArrayList<Object> requestList = getReq(status);
	ArrayList<Object> deadlineList = getlistOfDeadlines(status);
	int amount;
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("listOfRequestsToAssign");
	answer.add( (String)(requestList.get(1))); // number of items in the list
	answer.add(requestList); // list of requests
	answer.add(deadlineList); // list of deadlines
	
	return answer;
}

/**
 * 
 * This method is used to build a list of users in the system who all share the same access level
 * The method builds an ArrayList of UserEntities and initiates their values
 * @param userSkill
 * @return a list of employees
 */
public ArrayList<Object> getListOfUsers(String userSkill) 
{
	ArrayList<Object> listOfUsersWithSkill = new ArrayList<Object>();
	ArrayList<Object> listOfUid = new ArrayList<Object>();
	ArrayList<Object> listOfNames = new ArrayList<Object>();
	ArrayList<Object> tempList = new ArrayList<Object>();
	String firstName;
	String lastName;
	listOfUsersWithSkill.add("listOfUsersWithSkill");
	int amount,i;
	
	try {
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		
		ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.users WHERE " + userSkill + " = '1' ;");
		ResultSet rs2 = stmt2.executeQuery("SELECT Uid FROM test.users WHERE " + userSkill + " = '1' ;");	
		
		if(!rs.next())
		{
			listOfUsersWithSkill.add("Error: No users with the " + userSkill + " skill.");
			listOfUsersWithSkill.add("0");
		}
		else
		{
			listOfUsersWithSkill.add("Valid");
			listOfUsersWithSkill.add(rs.getString(1)); // number of rows in the table
			amount = Integer.parseInt(rs.getString(1));	

			while(rs2.next())
			{
				String uid = new String (rs2.getString(1));
				listOfUid.add(uid); // add uids to the list
				
			}
			
			for ( i =0; i< amount ; i++)
			{
				tempList = getInfo((String)listOfUid.get(i));
				firstName = (String)tempList.get(1);
				lastName = (String)tempList.get(2);
				String name = new String  (firstName + " " + lastName);
				listOfNames.add(name);
			}
			
			listOfUsersWithSkill.add(listOfUid);
			listOfUsersWithSkill.add(listOfNames);
			
		}
		
		stmt.close();
		stmt2.close();
		rs.close();
		rs2.close();
		
		
	} catch (SQLException e) 
	{
		listOfUsersWithSkill.add("Error: No users with the " + userSkill + " skill.");
		listOfUsersWithSkill.add("0");
			//e.printStackTrace();
	}
	
	return listOfUsersWithSkill;
}

/**
 * 
 * @param  reqID is a parameter indicating the id of the desired request
 * @param durationis a parameter that gets the relevant duration of the request
 * 	The method searches the DB for a certain request to find its relevant duration
 * @return array list containing requests duration
 */
public ArrayList<Object> getDu(String reqID) {
	// TODO Auto-generated method stub
	ArrayList<Object> durations = new ArrayList<Object>();
	
	int duration;

	durations.add("duration");
	try {
	Statement stmt= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT Duration FROM test.request WHERE Rid="+reqID);
	rs.next();
	
	
	durations.add((Integer)rs.getInt(1));
	stmt.close();
	rs.close();
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	durations.add("Fail");
	//e.printStackTrace();
	}
	return durations;

}
/**
 * 
 * @param  reqID is a parameter indicating the id of the desired request
 * @param newDurationis a parameter that contains a new duration value for the request
 * 	The method searches the DB for a certain request to update its duration field with a value 
 * @return updateDuration if successful, unableToupdateDuration otherwise 
 */
public ArrayList<Object> setNewDuration(String reqID, int newDuration) {
	// TODO Auto-generated method stub
ArrayList<Object> durationOp = new ArrayList<Object>();

	try {
	Statement stmt= conn.createStatement();
	stmt.executeUpdate("UPDATE test.request SET Duration = '" + newDuration + "' WHERE Rid =" +reqID);
	
	stmt.close();
	
	durationOp.add("updateDuration");
	} catch (SQLException e) {
	// TODO Auto-generated catch block
		durationOp.add("unableToupdateDuration");
	//e.printStackTrace();
	}
	return durationOp;
}
/**
 * This method was Built in order to be able to Update a saved Executed request while pressing the Save Button in the ExecutionLeader->Execute->temp Save button 
 * @param code
 * @param request
 * @param details
 * @return an answer of Vaild or an Valid (Go\noGo)
 */

public ArrayList<Object> updateExecutedReq(String code, String request,String det)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(code);
	try {
	
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("UPDATE test.executed SET executionDetails = '" + det + "' WHERE rid1 =" +request);
		
		stmt.close();
	answer.add("Valid");
}
catch(Exception r){
	answer.add("notValid");
}

	return answer;


}
/**
 * 
 * This method pairs and inspector to a request 
 * to inspect it's execution.
 * @param requestID
 * @param inspectorID
 * @return validation of the operation
 */
public ArrayList<Object> setInspectorToRequest(String requestID,String inspectorID)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("inspectorAssigned");
	 changeStatus(requestID,"12");
	 int res=0;
	 
	 try {	
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			Statement stmt4= conn.createStatement();
			
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM test.exectuioninspection WHERE requestID2 = '" + requestID +"';");


			if (!rs.next()) // returns false if empty
			{	
				stmt4.executeUpdate("INSERT INTO test.exectuioninspection SET inspectionID =NULL, requestID2 = '" + requestID + "', inspectorID = '" + inspectorID + "', wasExtended = 'no';");
										
			}
			else // there's already an inspection in the list, clean all fields
			{	
			
				res = stmt2.executeUpdate("UPDATE test.exectuioninspection SET faults = NULL WHERE requestID2 =  '" + requestID + "', wasExtended= no;");
				
			}
	
			String today = CommonMethods.getDate();
			String newdeadline = CommonMethods.addDurationToDate(today, 7);
			
			//Set new deadline
			setValueByRequestID ("request", "Deadline", "Rid", requestID, newdeadline, "dummyCode" );
			if (res==1)
			{
				answer.add("inspectorAssigned");
			}
			else answer.add("Error: in inspection table");
			
			stmt.close();
			stmt2.close();
			stmt4.close();
			rs.close();
			
			
			
				
		} 
		catch (SQLException e)
		{
			answer.add("Error: in inspection table");
			
		}	
	 
	 

return answer;
	
}

/**
 * This method was Built in order to be able to Update a saved Executed request while pressing the Save Button in the ExecutionLeader->Execute->temp Save button 
 * @param code
 * @param request
 * @param details
 * @return an answer of Vaild or an Valid (Go\noGo)
 *//*
public ArrayList<Object> updateExecutedReq(String code, String request,String det)
	{
				ArrayList<Object> answer = new ArrayList<Object>();
				answer.add(code);
				try {
					Statement stmt= conn.createStatement();
					stmt.executeUpdate("UPDATE test.executed SET executionDetails = '" + det + "' WHERE rid1 =" +request);
					stmt.close();
				answer.add("Valid");
			}
			catch(Exception r){
				answer.add("notValid");
			}
	
		return answer;

	}
	*/
/*
public ArrayList<Object> convertRidToExeID(String requestID)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("requestIDConvertedToExecutionID");
	
	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT idexecuted FROM test.executed WHERE rid1 = '" + requestID + "';");

		if (!rs.next()) // empty list
		{
			answer.add("Invalid. No execution found.");
			answer.add("0");
			
		}
		else
		{
			answer.add("Valid");
			answer.add(rs.getString(1));
		}
			
				
		stmt.close();
		rs.close();
		
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		answer.add("Invalid. Error with SQL database.");
		answer.add("0");
			e.printStackTrace();
	}
	
	return answer;
}

*/


/**
 * 
 * This method builds an inspection entity, initiates it's values and returns it
 * @param rid - the request ID
 * @return the entity
 */
public ArrayList<Object> getInspection(String rid)
{
	ArrayList<Object> answer = new ArrayList<Object>();
		
	answer.add("returnedInspection");

	
	try {
			
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
		
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.exectuioninspection WHERE requestID2 = '" + rid +"';");
		ResultSet rs2 = stmt2.executeQuery("SELECT Deadline FROM test.request WHERE Rid = '" + rid +"';");
		
		
		InspectionEntity inspection = new InspectionEntity();
		
		rs2.next();
		inspection.setDeadline(rs2.getString(1));
		
		
	

			if (!rs.next()) // returns false if empty
			{
				answer.add("Error:No Inspection.");
			
			}
			else
			{	
				
				answer.add("Valid");
				inspection.setInspectionID(rs.getString(1));
				inspection.setRequestID(rs.getString(2));
				inspection.setInspectorID(rs.getString(3));
				inspection.setFaults(rs.getString(4));
				inspection.setWasExtended(rs.getString(5));				
			}

		answer.add(inspection);	
		
		stmt.close();
		stmt2.close();
		rs.close();
		rs2.close();
			
	} 
	catch (SQLException e)
	{
	//	e.printStackTrace();
	}	

return answer;
}
/**
 * 
 * This method sets an inspection in the database
 * @param inspection an Inspection Entity holding the inspection details
 * @param command - A string that changes the returned code
 * @return validation of the operation
 */
public ArrayList<Object> setInspection (InspectionEntity inspection, String command)
{	
	ArrayList<Object> answer = new ArrayList<Object>();
	if (command.equalsIgnoreCase("approve"))
		answer.add("inspectionSet");
	else
		if(command.equalsIgnoreCase("save"))
			answer.add("inspectionSaved");
		else
			answer.add("inspectionDenied");
	
	try
	{
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("UPDATE test.exectuioninspection SET faults = '" + inspection.getFaults()  + "' WHERE requestID2 = '" + inspection.getRequestID() + "';");
		answer.add("Valid");		
		stmt.close();
	}
	catch (SQLException e)
	{
		answer.add("Invalid");
		//e.printStackTrace();
	}
return answer;
}
/**
 * 
 * 
 * 	The method searches the DB for a relevant extend request 
 * @return array list containing the requests ID's with relevant extend requests
 */
public ArrayList<Object> getExtend() {
	// TODO Auto-generated method stub
	ArrayList<Object> extend = new ArrayList<Object>();
	String rid;
	String status;
	String extid;

	extend.add("extendlist");
	

	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT e.requestID FROM extenddeadlinerequests e , request r WHERE (e.requestID= r.Rid) AND (e.oldStatus=r.Status)");
	ResultSet rs2 = stmt2.executeQuery("SELECT COUNT(*) FROM extenddeadlinerequests e , request r WHERE (e.requestID= r.Rid) AND (e.oldStatus=r.Status)");
	rs2.next();
	extend.add(rs2.getString(1));
	
	while(rs.next())
	{
			extend.add(rs.getString(1));
	}
	if(extend.get(1).equals("0"))
	{
		extend.add("0");
		extend.add("EmptyList1");
	}
	
	
	stmt.close();
	stmt2.close();
	
	
	rs.close();
	rs2.close();
	
	} catch (SQLException e) {
	// TODO Auto-generated catch block
		extend.add("Fail");
//	e.printStackTrace();
	}
	return extend;
}
/**
 * 
 * @param  reqID is a parameter indicating the id of the desired request
 * 	The method searches the DB for a certain extend request to find its relevant info
 * @return array list containing the desired extend request info
 */
public ArrayList<Object> getExtendInfo(String reqID) {
	// TODO Auto-generated method stub
	ArrayList<Object> extendInfo = new ArrayList<Object>();
	

	extendInfo.add("extendInfo");
	
	try {
	Statement stmt= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT * FROM test.extenddeadlinerequests  WHERE requestID="+reqID);
	rs.next();
	extendInfo.add(rs.getString(3));
	extendInfo.add(rs.getString(5));
	
	stmt.close();

	rs.close();

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		extendInfo.add("Fail");
	//e.printStackTrace();
	}
	return extendInfo;
}

/**
 * 
 * This method builds a User Entity for each employee, and counts the number of employees
 * @returnThis an array list with the number of employees in the list and user entities of all employees.
 */
public ArrayList<Object> initializeMangeEmployees() 
{
	ArrayList<Object> answer = new ArrayList<Object>();
	UserEntity tempUser;
	int amount=0;
	answer.add("initializeMangeEmployees");
	answer.add( Integer.toString(amount) );
	
	
	try
	{
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT u.uid, o.FirstName, o.LastName, u.Supervisor, u.Assessor, u.ExecutionLeader, u.CommitteeMember, u.Chairman, o.Email, o.PhoneNum, o.Title, o.Department FROM test.users u, test.outer o WHERE ( u.Uid = o.Oid ) AND ( (u.Supervisor = 1) OR (u.Assessor = 1) OR (u.ExecutionLeader = 1) OR (u.CommitteeMember = 1) OR (u.Chairman = 1) );");
		
		while (rs.next())
		{
			amount++;
			
			tempUser = new UserEntity("","");
			tempUser.setUserID(rs.getString(1));
			tempUser.setFirstName(rs.getString(2));
			tempUser.setLastName(rs.getString(3));
			tempUser.setIsSupervisor(rs.getString(4));
			tempUser.setIsAssessor(rs.getString(5));
			tempUser.setIsExecutionLeader(rs.getString(6));
			tempUser.setIsCommitteeMember(rs.getString(7));
			tempUser.setIsChairman(rs.getString(8));
			tempUser.setEmail(rs.getString(9));
			tempUser.setPhone(rs.getString(10));
			tempUser.setTitle(rs.getString(11));
			tempUser.setDepartment(rs.getString(12));
			
			answer.add(tempUser);
		}
			answer.set(1, Integer.toString(amount));
		
		stmt.close();
	}
	catch (SQLException e)
	{
		answer.add("0");
	//	e.printStackTrace();
	}
	
	
return answer;
	
	
	
}
/**
 * 
 * @param  code is an indicator whether to approve or deny an extend request
 * @param extID is a parameter including the extend id
 * @param newDeadline is the new deadline after the extension
 * @param rid contains the request id refer by the extend id
 * 	The method searches the DB for a certain extend request and approves or denys it 
 * @return fail if failed 
 */

public ArrayList<Object> approvDenyExt(String code, String extID) {
	// TODO Auto-generated method stub
ArrayList<Object> approvDenyExt = new ArrayList<Object>();
	String newDeadline;
	String rid;


if(code.equals("1"))
{
	approvDenyExt.add("approvExt");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();
	Statement stmt3= conn.createStatement();
	

	ResultSet rs = stmt.executeQuery("SELECT * FROM test.extenddeadlinerequests  WHERE ExtID="+extID);
	rs.next();
	rid=rs.getString(2);
	newDeadline=rs.getString(5);

	stmt2.executeUpdate("UPDATE test.request SET Deadline = '" + newDeadline  + "' WHERE Rid = " + rid );
	stmt3.executeUpdate("DELETE FROM test.extenddeadlinerequests  WHERE ExtID="+extID);
	
	
	stmt.close();
	stmt2.close();
	stmt3.close();
	rs.close();

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		approvDenyExt.add("Fail");
//	e.printStackTrace();
	}
}
else
if(code.equals("0"))
{
	approvDenyExt.add("DenyExt");
	try {
	Statement stmt= conn.createStatement();


	stmt.executeUpdate("DELETE FROM test.extenddeadlinerequests  WHERE ExtID="+extID);
	
	stmt.close();


	} catch (SQLException e) {
	// TODO Auto-generated catch block
		approvDenyExt.add("Fail");
	//e.printStackTrace();
	}
}

	return approvDenyExt;
}

/**
 * 
 * This method switches the chairman access level of two users
 * @param oldChairman loses the chairman access
 * @param newChairman gains chairman access
 * @return validation for the operation
 */
public ArrayList<Object> switchChairman(UserEntity oldChairman,	UserEntity newChairman)
{
	String oldChairmanUserID = oldChairman.getUserID();
	String newChairmanUserId = newChairman.getUserID();
		
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("chairmanSwitched");
	 
	 try {	
		
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();

			stmt.executeUpdate("UPDATE test.users SET Chairman = 1 WHERE Uid =  '" + newChairmanUserId + "';");						
			stmt2.executeUpdate("UPDATE test.users SET Chairman = 0 WHERE Uid =  '" + oldChairmanUserID + "';");
			
			stmt.close();
			stmt2.close();	
			answer.add("Valid");
		} 
		catch (SQLException e)
		{
			answer.add("Error: Cannot switch chairmans");
			//e.printStackTrace();
		}	
	 
	 

return answer;
}
/**
 * 
 * @param  assessmentEntity
 * 	The method sets a new row to assessment table in the database with the request number 
 * @return an ArrayList with 'NewAssessmentRowSet' if succeeded 
 */
public ArrayList<Object> newAssessmentRow(AssessmentEntity assessmentEntity)
{
	ArrayList<Object> answer = new ArrayList<Object>();
		
	
	try {		
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();
			answer.add("NewAssessmentRow");
			ResultSet rs = stmt2.executeQuery("SELECT * FROM test.assessment WHERE Rid = '" + assessmentEntity.getRid() +"';");

			if(!rs.next()){// no row in assessment for this rid
				try
				{
				stmt.executeUpdate("INSERT INTO assessment(Rid) VALUES('" + assessmentEntity.getRid() +"');");
				
				answer.add("NewAssessmentRowSet");
				}
				catch (SQLException e)
				{
					answer.add("NewAssessmentRowNotSet");
				}
			}
			else
			{
				assessmentEntity.setLocation(rs.getString(3));
				assessmentEntity.setDurationAssessment(rs.getString(4));
				assessmentEntity.setChangeDescription(rs.getString(5));
				assessmentEntity.setBenefit(rs.getString(6));
				assessmentEntity.setConstrictions(rs.getString(7));
				assessmentEntity.setReviewed(rs.getString(8));
				answer.add("ExistingAssessmentRow");
				answer.add(assessmentEntity);
			}
			stmt.close();
			stmt2.close();
			rs.close();
			
			
		}catch (SQLException e){
		// TODO Auto-generated catch block
			answer.add("NewAssessmentRowNotSet");
			//e.printStackTrace();
		}	
	return answer;
}

/**
 * 
 * This method switches the CommitteeMembers access level of a user and an existing Committee Member
 * @param oldCommitteeMember loses the CommitteeMember access
 * @param newCommitteeMember gains CommitteeMember access
 * @return validation for the operation
 */
public ArrayList<Object> switchCommitteeMembers(UserEntity oldCommitteeMember, UserEntity newCommitteeMember)
{
	
	String oldCommitteeMemberUserID = oldCommitteeMember.getUserID();
	String newCommitteeMemberUserID = newCommitteeMember.getUserID();
		
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("CommitteeMemberSwitched");
	 
	 try {	
			Statement stmt= conn.createStatement();
			Statement stmt2= conn.createStatement();


			stmt.executeUpdate("UPDATE test.users SET CommitteeMember = 1 WHERE Uid =  '" + newCommitteeMemberUserID + "';");						
			stmt2.executeUpdate("UPDATE test.users SET CommitteeMember = 0 WHERE Uid =  '" + oldCommitteeMemberUserID + "';");
			
			stmt.close();
			stmt2.close();	
			answer.add("Valid");
		} 
		catch (SQLException e)
		{
			answer.add("Error: Cannot switch committee members");
			//e.printStackTrace();
		}	


return answer;
}
/**
 * 
 * This method is used in cases we wishes to get all the requestes that were not approved after execution .
 * @param code
 * @param pid
 * @param oldStatus
 * @return array list with all the entities .
 */
public ArrayList<Object> getDeniedExecutions(String code ,String pid,String oldStatus ) 
{
	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(code);
	String rid;
	InspectionEntity ee ;
	int num=0;
	int num2=0;
	int totalEnt=0;
	int j;
	int i;
	try{
		Statement stmt= conn.createStatement();
		Statement stmt2= conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT Rid from test.request where Elid ='" + pid + "' AND Status = '"+ oldStatus +"' ");
			rs.last();
			num = rs.getRow();
			rs.first();
			for(i=0;i<num;i++)
			{
				rid = Integer.toString(rs.getInt(1));
				ResultSet rs2 = stmt2.executeQuery("Select requestID2,faults from exectuioninspection where requestID2="+rid);
				rs2.last();
				num2 = rs2.getRow();
				rs2.first();
				for(j=0;j<num2;j++)
				{
					ee= new InspectionEntity();
					ee.setInspectionID(rs2.getString(1));
					ee.setFaults(rs2.getString(2));
					answer.add(ee);
					rs2.next();
					totalEnt++;
				}
				rs.next();
			}
			answer.add(1,totalEnt);
			stmt.close();
			stmt2.close();
	}
	catch (Exception e)
	{
		num = 0;
		answer.add(1,num);
	
		return answer;
	}
	return answer;
}
/**
 * 
 * This method is used in order to delete a record from the executed table after a request
 * is "recovered" from beeing denied for execution. 
 * @param rid
 * @return valid\notValid
 */
public ArrayList<Object> DelExeReq(String rid)
{
	
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("changeStatusAndDel");
	try
	{
		Statement stmt = conn.createStatement();	
		int del= stmt.executeUpdate("DELETE from test.executed where rid1="+rid);
		stmt.close();
		answer.add("Valid");
	}
	catch(Exception e)
	{
		answer.add("notValid");
	}
	return (answer);
}

public ArrayList<Object> setUserAccess(UserEntity currentUser)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("userAccessWasSet");
	 
	 try {	
			Statement stmt= conn.createStatement();

			stmt.executeUpdate("UPDATE test.users SET Supervisor = '" + currentUser.getIsSupervisor() + "', Assessor = '" + currentUser.getIsAssessor() + "', ExecutionLeader = '" + currentUser.getIsExecutionLeader() + "' WHERE Uid =  '" + currentUser.getUserID() + "';");						
			
			stmt.close();
			answer.add("Valid");
		} 
		catch (SQLException e)
		{
			answer.add("Error: Cannot set user access");
			//e.printStackTrace();
		}	
	 
	 

return answer;
}
/**
 * 
 * 	This method is used to get requests with status 61 and change their status to 4
 * @return an ArrayList with 'statusSet' if succeeded 
 */
public ArrayList<Object> changeStatus(){
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("changeStatus");
	try{
		Statement stmt= conn.createStatement();
		stmt.executeUpdate("UPDATE test.request SET Status = 4 WHERE Status = 61;");
		stmt.close();
		answer.add("statusSet");
	}
	catch(SQLException e)
	{
		answer.add("StatusNotSet");
	//	e.printStackTrace();
	}
	return answer;
}
/**
 * this method gets all user entities wich coResponds with the condition, in order to send them back to be used when sending mails.
 * in order to get the email address of each user .
 *created by : adam daxa  
 * @return
 */
public ArrayList<Object> getUsers (String code,String id)
{
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add(code);
	try
	{
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("Select Oid,Email from test.outer where Oid ="+id);
		
		while(rs.next())
		{
			String oid = rs.getString(1);
			String mail = rs.getString(2);
			EmailEntity em = new EmailEntity();
			em.setOid(oid);
			em.setMail(mail);
			answer.add("Valid");
			answer.add(em);
		}
		stmt.close();
		rs.close();
	}
	catch(Exception e)
	{
		answer.add("NotValid");
	}
	
	return answer;
}

/**
 * 
 * @param reqID is a parameter indicating the id of the desired request
 * @param currents is the current status field content
 * @param change is the change description field content
 * @param explanation is the Explanation field content
 * 	The method  updates the DB of a certain request, updating its info
 * @return sucsess if succeeded ,fail if failed 
 */
public ArrayList<Object> updateFields(String reqID, String currentS,String change, String explanation, String supervisorID)
{
	// TODO Auto-generated method stub
	ArrayList<Object> updateFields = new ArrayList<Object>();
	
	updateFields.add("updateFields");
	try {
	Statement stmt= conn.createStatement();
	
	int result=stmt.executeUpdate("UPDATE test.request SET CurrentDescription = '" + currentS  + "', ChangeDescription = '" + change + "', Explanation = '" + explanation +"' WHERE Rid = " + reqID+";" );

	
	stmt.close();
	if(result==1)
	{
	updateFields.add("success");
	}
	else
	if(result==0)
	{
	updateFields.add("Fail");
	}
	createChangeReport(reqID, currentS,change, explanation, supervisorID);
	
	}
	catch (SQLException e)
	{
	// TODO Auto-generated catch block
		updateFields.add("Fail");
	//e.printStackTrace();
	}
	
	return updateFields;
}


public ArrayList<Object> completeReq(String status1, String status2) {
	// TODO Auto-generated method stub
	ArrayList<Object> completeReq = new ArrayList<Object>();
	completeReq.add("completeReq");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.request WHERE (Status= '"+status1+"') OR (Status='"+status2+"');");
	ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test.request WHERE (Status='"+status1+"') OR (Status='"+status2+"');");

	rs.next();
	completeReq.add(rs.getString(1)); // number of rows in the table
	while(rs2.next())
	{	
		completeReq.add(rs2.getString(1)); // add requests to the list
	}
	stmt.close();
	stmt2.close();
	rs.close();
	rs2.close();
	if(completeReq.get(1).equals("0"))
	{
		completeReq.add("0");
		completeReq.add("EmptyList1");
	}

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		completeReq.add("0");
		completeReq.add("EmptyList1");
	//e.printStackTrace();
	}

	return completeReq;
	
}

/**
 * 
 * This method creates a record of a change done to a request in the database by the supervisor
 * @param reqID - request iD
 * @param currentS - current description
 * @param change - requested change
 * @param explanation - explanation fo the change
 * @param supervisorID - supervisor user ID
 */
private void createChangeReport (String reqID, String currentS, String change, String explanation, String supervisorID)
{
	 
	 try {	
			Statement stmt= conn.createStatement();


			stmt.executeUpdate("REPLACE test.requestChanges SET requestID3 = '" + reqID + "', supervisorID = '" + supervisorID + "', dateOfchange = '" + CommonMethods.getDate() + "', changeDescription = '" + currentS + "\n\n" + change + "\n\n" + explanation + "' ;");		
			
			stmt.close();
			
		} 
		catch (SQLException e)
		{
			
		//	e.printStackTrace();
		}	
	 
}
/**
 * 
 * This method logs the user off and resets the database's isLoggefIn flag
 * @param userID - the user ID
 */
public void logOff(String userID)
{
	
	 
	 try {	
			Statement stmt= conn.createStatement();

			stmt.executeUpdate("UPDATE test.users SET isLoggedIn = 'no';");						
			
			stmt.close();
		} 
		catch (SQLException e)
		{
			
			//e.printStackTrace();
		}	
}
/**
 * 
 * @param  request
 * @param  bytes
 * @param  fileLength
 * 	The method sets an uploaded file to the 'File' field in the request table for the giving request
 * @return an ArrayList with 'fileUploaded' if succeeded 
 */
public ArrayList<Object> uploadFile(RequestEntity request,byte[] bytes, int fileLength) {

	ArrayList<Object> answer = new ArrayList<Object>();

	answer.add("uploadeFile");

	Statement stmt;

	try {

		stmt = conn.createStatement();
		PreparedStatement psmt = null;

		psmt = conn.prepareStatement("UPDATE request SET File=? WHERE Rid= LAST_INSERT_ID()");

		SerialBlob sb = new SerialBlob(bytes);
		psmt.setBlob(1, sb);
//		psmt.setInt(2, request.getRID());

		int s = psmt.executeUpdate();
		answer.add("fileUploaded");
	

	} catch (SQLException e) {
		//e.printStackTrace();
		answer.add("fileNotUploaded");
		
	}

	return answer;

}
/**
 * 
 * @param  request
 * @param  bytes
 * @param  fileLength
 * 	The method gets a file from the 'File' field in the request table for the giving request
 * @return an ArrayList with 'valid' if succeeded 
 */
public ArrayList<Object> getFile(String request) {

	ArrayList<Object> answer = new ArrayList<Object>();
	String len = new String();
	answer.add("viewFile");

	Statement stmt;

	try 
	{
		stmt = conn.createStatement();

		ResultSet rs2;
		rs2 = stmt.executeQuery(" SELECT OCTET_LENGTH(File) FROM request WHERE Rid='"+ request + "';");

		while (rs2.next()) 
		{
			len = rs2.getString(1);
		}
		
		rs2.close();
		if(len!=null)
		{
			answer.add("valid");
			ResultSet rs;
			rs = stmt.executeQuery("SELECT * FROM request WHERE Rid='"+ request + "';");
			while (rs.next()) 
			{
				InputStream bs = rs.getBinaryStream("File");
				int read = 0;
				byte[] bytes = new byte[Integer.parseInt(len)];
				Number randomFileNumber = Math.random() * 1000;
				BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(randomFileNumber.toString()+ ".pdf"));
				while ((read = bs.read(bytes)) > 0) 
				{
					bos.write(bytes);
				}
				bos.flush();
				bos.close();
				answer.add(bytes);
			} 			
		}
		else
		{
			
			answer.add("notValid");
		}
		stmt.close();
	} 
	catch (SQLException e) {
		// TODO Auto-generated catch block
	
		//e.printStackTrace();
	}
	catch (FileNotFoundException e) {
		// TODO: handle exception
		
	//	e.printStackTrace();
	}
	catch (IOException e) 
	{
		// TODO Auto-generated catch block
		
		//e.printStackTrace();
	}
	catch (Exception e) {
		// TODO: handle exception
		
		//e.printStackTrace();
	}

	return answer;
}


/**
 * 
 * @param exeID is a parameter indicating the executed ID
 * @param 
 * 
 *  The method  finds the executed tuple coorisponding with the exeID and takes her info
 * @return info of the executed tuple or- no exe error 
 */
public ArrayList<Object> getExecute(String exeID) {
	// TODO Auto-generated method stub

	ArrayList<Object> getExecute = new ArrayList<Object>();
	
	getExecute.add("getExecute");
	

	try {
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM test.executed WHERE idexecuted =" + "'" + exeID + "'" + ";");
		
		rs.next();
		String idexe = new String (rs.getString(1));
		String rid = new String (rs.getString(2));
		String exeDet = rs.getString(3);
		String wasExtend = rs.getString(4);
		
		
		Statement stmt2= conn.createStatement();
		ResultSet rs2 = stmt2.executeQuery("SELECT Elid FROM test.request WHERE Rid = '" + rid + "';"); // get Assessor ID
		
		rs2.next();
		String elID = rs2.getString(1);
		
		
		ExecutionEntity exe = new ExecutionEntity ();
		exe.setExecutionDetails(exeDet);
		Integer rrid=Integer.parseInt(rid);
		exe.setRid(rrid);
		exe.setWasExtended(wasExtend);
		
		
		getExecute.add("Valid");
		getExecute.add(exe);
		
		stmt.close();
		stmt2.close();
		
		rs.close();
		rs2.close();
		
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		getExecute.add("No exe Error.");
	//	e.printStackTrace();
	}
	
	return getExecute;
}


/**
 * 
 * @param rID is a parameter indicating the request ID
 * @param 
 * 
 *  The method  finds the assessorID  coorisponding with the  request id 
 * @return Relevant Aid and gotit or fail
 */
public ArrayList<Object> ridToAid(String rID) {
	// TODO Auto-generated method stub
	ArrayList<Object> answer = new ArrayList<Object>();
	answer.add("ridToAid");
	try{
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Aid FROM test.request WHERE Rid =" + "'" + rID + "'" + ";");
		rs.next();
		answer.add("gotit");
		answer.add(rs.getString(1));
		
		stmt.close();
		rs.close();
		
	}
	catch(SQLException e)
	{
		answer.add("fail");
	//	e.printStackTrace();
	}
	return answer;
}

public String requestToUserID (String requestID)
{
	String answer="none";
	
	try{
		Statement stmt= conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Uid1 FROM test.request WHERE Rid =" + "'" + requestID + "'" + ";");
		rs.next();
		
		answer = rs.getString(1);
		stmt.close();
		rs.close();
		
	}
	catch(SQLException e)
	{
		
	//	e.printStackTrace();
	}

	return answer;
}

/**
 * @Author Guy Franco
 * 
 * 	
 * @return returns all requests ID  or emptylist1 if non are found
 */

public ArrayList<Object> getAllReq() {
	// TODO Auto-generated method stub
	ArrayList<Object> allReq = new ArrayList<Object>();
	allReq.add("allReq");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();

	ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM test.request WHERE Status!= '0' ");
	ResultSet rs2 = stmt2.executeQuery("SELECT Rid FROM test.request WHERE Status!= '0'");

	rs.next();
	allReq.add(rs.getString(1)); // number of rows in the table
	while(rs2.next())
	{	
		allReq.add(rs2.getString(1)); // add requests to the list
	}
	stmt.close();
	stmt2.close();
	rs.close();
	rs2.close();
	if(allReq.get(1).equals("0"))
	{
		allReq.add("0");
		allReq.add("EmptyList1");
	}

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		allReq.add("0");
		allReq.add("EmptyList1");
	e.printStackTrace();
	}

	return allReq;
}
/**
 * @Author Guy Franco
 * @param rID is a parameter indicating the request ID
 * @param reason is a parameter indicating the suspention reason
 * @return suspend requests "updateSuccesful" if good "0" otherwise
 */

public ArrayList<Object> suspend(String rID, String reason) {
	// TODO Auto-generated method stub
	ArrayList<Object> suspend = new ArrayList<Object>();
	suspend.add("suspend");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt1= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT Status FROM test.request WHERE Rid =" + "'" + rID + "'" + ";");
	rs.next();
	String status= rs.getString(1);
	stmt1.executeUpdate("INSERT INTO test.suspended SET susid = '" + rID + "', oldStatus = '" + status + "', reason = '" + reason + "';");

	changeStatus(rID,"0");
	
	stmt.close();
	rs.close();
	suspend.add("updateSuccessful");

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		suspend.add("0");
		suspend.add("EmptyList1");
	e.printStackTrace();
	}

	return suspend;
}
/**
 * @Author Guy Franco
 * @param rID is a parameter indicating the request ID
 * 
 * @resumes suspended requests "updateSuccesful" if good "0" otherwise
 */
public ArrayList<Object> resume(String rID) {
	// TODO Auto-generated method stub
	ArrayList<Object> resume = new ArrayList<Object>();
	resume.add("resume");
	try {
	Statement stmt= conn.createStatement();
	Statement stmt2= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT oldStatus FROM test.suspended WHERE susid =" + "'" + rID + "'" + ";");
	rs.next();
	String status= rs.getString(1);
	
	changeStatus(rID,status);
	stmt2.executeUpdate("DELETE FROM test.suspended  WHERE susid="+rID);
	stmt.close();
	stmt2.close();
	rs.close();
	resume.add("updateSuccessful");

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		resume.add("0");
		resume.add("EmptyList1");
	e.printStackTrace();
	}

	return resume;
}
/**
 * @Author Guy Franco
 * @param rID is a parameter indicating the request ID
 * 
 * @returns the suspention reason
 */

public ArrayList<Object> susInfo(String rID) {
	// TODO Auto-generated method stub
	ArrayList<Object> susInfo = new ArrayList<Object>();
	susInfo.add("susInfo");
	try {
	Statement stmt= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT reason FROM test.suspended WHERE susid =" + "'" + rID + "'" + ";");
	rs.next();
	susInfo.add(rs.getString(1));

	stmt.close();
	rs.close();
	susInfo.add("updateSuccessful");

	} catch (SQLException e) {
	// TODO Auto-generated catch block
		susInfo.add("0");
		susInfo.add("EmptyList1");
	e.printStackTrace();
	}

	return susInfo;
}

public int getexceedTime(String requestID)
{
	int days=0;
	try {
	Statement stmt= conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT exceedTime FROM test.exceeded WHERE rid =" + "'" + requestID + "'" + ";");

	if(!rs.next())
	{
		days = Integer.parseInt((rs.getString(1)));
	}

	stmt.close();
	rs.close();
	
	} catch (SQLException e)
	{
	// TODO Auto-generated catch block

	//e.printStackTrace();
	}
	
	return days;
}









}












